package org.jinion.database;

import java.sql.SQLException;
import java.util.Map;

import org.jinion.constants.OperatorEnum;
import org.jinion.result.AbstractResult;
/**
 * This class provides interfaces for database operations.<br>
 * Check concrete implementation to see if it is thread safe.
 * 
 * @author zhlmmc
 */
public interface IDatabase {
	/**
	 * Do a query by the given SQL. <br>
	 * Note: Only single queries are supported! 
	 * 
	 * @param sql the standard sql
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult querySql(String sql) throws SQLException;

	/**
	 * Do a query by the given sql and parameters.
	 * Note: Only single queries are supported! 
	 * 
	 * @param sql sql with prepared parameters eg. "SELECT * FROM user where id=?"
	 * @param parameters - parameters for sql
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult querySql(String sql, String[] parameters) throws SQLException;

	/**
	 * Execute the given query.
	 * 
	 * @param sql standard sql
	 * @return the execution result 
	 * @throws SQLException
	 */
	public AbstractResult executeSql(String sql) throws SQLException ;

	/**
	 * Execute the given sql with the given parameters.
	 * 
	 * @param sql sql with preparaed paremeters eg. "UPDATE user SET name=?". 
	 * @param parameters - parameters for sql.
	 * @return the execution result
	 * @throws SQLException
	 */
	public AbstractResult executeSql(String sql, String[] parameters) throws SQLException;
	
	/**
	 * Execute a update sql. This sql must start with "UPDATE".
	 * 
	 * @param sql standard update sql.
	 * @return	The number of affected rows.
	 * @throws SQLException
	 */
	public AbstractResult executeUpdateSql(String sql) throws SQLException ;
	
	/**
	 * Execute a insert sql. This sql must start with "INSERT".
	 * 
	 * @param sql standard insert sql.
	 * @return	The id of the new inserted record. 
	 * @throws SQLException
	 */
	public AbstractResult executeInsertSql(String sql) throws SQLException ;
	
	/**
	 * Do a query to the specified table. The effect of this method is same with <br>
	 * <i>SELECT * FROM tableName...</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param tableName the name of the table to be queried. 
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String tableName) throws SQLException;
	
	/**
	 * Do a query to the specified table. The effect of this method is same with <br>
	 * <i>SELECT * FROM tableName...ORDER BY orderBy</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param tableName the name of the table
	 * @param orderBy the order column name
	 * @return the returned data
	 */
	public AbstractResult query(String tableName, String orderBy) throws SQLException;
	
	/**
	 * Do a query to the specified table. The effect of this method is same with <br>
	 * <i>SELECT * FROM tableName...ORDER BY orderBy DESC(ASC)</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param tableName the name of the table
	 * @param orderBy the order column name
	 * @param ascOrDesc descend or ascend
	 * @return the returned data
	 */
	public AbstractResult query(String tableName, String orderBy, OperatorEnum ascOrDesc) throws SQLException;
	
	/**
	 * Do a query to the specified table. The effect of this method is same with <br>
	 * <i>SELECT fieldNames[0], fieldNames[1]... FROM tableName...</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param filedNames column to be selected
	 * @param tableName the name of the table
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String[] filedNames, String tableName) throws SQLException;
	
	/**
	 * Do a query to the specified table. The effect of this method is same with <br>
	 * <i>SELECT fieldNames[0], fieldNames[1]... FROM tableName...ORDER BY orderBy</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param filedNames columns to be selected
	 * @param tableName the name of the table
	 * @param orderBy the order column name
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String[] filedNames, String tableName, String orderBy) throws SQLException;
	
	/**
	 * Do a query to the specified table. The effect of this method is same with <br>
	 * <i>SELECT fieldNames[0], fieldNames[1]... FROM tableName...ORDER BY orderBy DESC(ASC)</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param filedNames columns to be selected 
	 * @param tableName the name of the table
	 * @param orderBy the order column name
	 * @param ascOrDesc descend or ascend
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String[] filedNames, String tableName, String orderBy, OperatorEnum ascOrDesc) throws SQLException;
	
	/**
	 * Do a query to the specified tables. The table name must be unique. <br> 
	 * The effect of this method is same with <br>
	 * <i>SELECT * FROM tableNames[0], tableNames[1]...</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param tableNames the names of tables
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String[] tableNames) throws SQLException;
	
	/**
	 * Do a query to the specified tables. The table name must be unique. <br> 
	 * The effect of this method is same with <br>
	 * <i>SELECT fieldNames[0], filedNames[1]...FROM tableNames[0], tableNames[1]...</i><br>
	 * If two tables have the same column name, add prefix "tableName." to the column. For examlpe: <br>
	 * <i>user.id</i><br>
	 * <i>user.name</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param fieldNames the columns to be selected
	 * @param tableNames the names of tables
	 * @return the returned value
	 * @throws SQLException
	 */
	public AbstractResult query(String[] fieldNames, String[] tableNames) throws SQLException;
	
	/**
	 * Do a query to the specified tables. The table name must be unique. <br> 
	 * The effect of this method is same with <br>
	 * <i>SELECT fieldNames[0], filedNames[1]...FROM tableNames[0], tableNames[1]...ORDER BY orderBy</i><br>
	 * If two tables have the same column name, add prefix "tableName." to the column. For examlpe: <br>
	 * <i>user.id</i><br>
	 * <i>user.name</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param fieldNames the columns to be selected
	 * @param tableNames the names of tables
	 * @param orderBy the order column name
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String[] fieldNames, String[] tableNames, String orderBy) throws SQLException;
	
	/**
	 * Do a query to the specified tables. The table name must be unique. <br> 
	 * The effect of this method is same with <br>
	 * <i>SELECT fieldNames[0], filedNames[1]...FROM tableNames[0], tableNames[1]...ORDER BY orderBy DESC(ASC)</i><br>
	 * If two tables have the same column name, add prefix "tableName." to the column. For examlpe: <br>
	 * <i>user.id</i><br>
	 * <i>user.name</i><br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param fieldNames the columns to be selected
	 * @param tableNames the names of tables
	 * @param orderBy the order column name
	 * @param ascOrDesc descent or ascend
	 * @return the returned data
	 * @throws SQLException
	 */
	public AbstractResult query(String[] fieldNames, String[] tableNames, String orderBy, OperatorEnum ascOrDesc) throws SQLException;
	
	/**
	 * Do a update to the specified table. All items with column name key in map will be updated. <br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param tableName the name of table
	 * @param data data to updated
	 * @return the number of affected rows.
	 * @throws SQLException
	 */
	public abstract AbstractResult update(String tableName, Map data) throws SQLException;
	
	/**
	 * Do a update to the specified table. Only items with filedNames key in map will be updated. <br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param fieldNames the columns to be updated
	 * @param tableName the name of table
	 * @param data data to be updated
	 * @return	the number of affected rows
	 * @throws SQLException
	 */
	public abstract AbstractResult update(String[] fieldNames, String tableName, Map data) throws SQLException;

	/**
	 * Do an insert to the specified table. All items with column name key in map will be inserted. <br>
	 * <b>NOTE: This method will clear where clauses</b>
	 * 
	 * @param tableName the name of table
	 * @param data data to be inserted
	 * @return	the id of the new inserted record
	 * @throws SQLException
	 */
	public abstract AbstractResult insert(String tableName, Map data) throws SQLException;

	/**
	 * Do an insert to the specified table. Only items with filedNames key in map will be inserted. <br>
	 * <b>NOTE: This method will clear where clauses</b>
	 *  
	 * @param fieldNames the columns to be updated
	 * @param tableName the name of table
	 * @param data data to be inserted
	 * @return	the id of the new inserted record 
	 * @throws SQLException
	 */
	public abstract AbstractResult insert(String[] fieldNames, String tableName, Map data) throws SQLException;
	
	/**
	 * Delete records from the given table. 
	 * 
	 * @param tableName the name of the table
	 * @return	whether the operation is successful or not
	 * @throws SQLException
	 */
	public abstract AbstractResult delete(String tableName) throws SQLException ;
	
	/**
	 * Add a where clause. This clause is an "and" clause if it is not at the head of the list. <br>
	 * The compare type of this method is IDatabase.COMPARE_TYPE_EQUAL. <br>
	 * This medhod assumes that if the string of <i>value</i> is the same with any column name, <br> 
	 * the <i>value</i> denotes the column not the string. If you want to use the string of the <br> 
	 * <i>value</i> instead of the column, add single quotes around <i>value</i>.
	 * 
	 * @param fieldName the column name
	 * @param value value of field
	 * @return current database for where chain
	 */
	public abstract IDatabase where(String fieldName, Object value);

	/**
	 * Add a where clause. This clause is an "and" clause if it is not at the head of the list.
	 * This medhod assumes that if the string of <i>value</i> is the same with any column name, <br> 
	 * the <i>value</i> denotes the column not the string. If you want to use the string of the <br> 
	 * <i>value</i> instead of the column, add single quotes around <i>value</i>.
	 * 
	 * @param fieldName the column name
	 * @param value value of field
	 * @param compareType	IDatabase.COMPARE_TYPE_EQUAL, IDatabase.COMPARE_TYPE_UNEQUAL
	 * 						, IDatabase.COMPARE_TYPE_GREATER_THAN, IDatabase.COMPARE_TYPE_LESS_THAN,
	 * 						IDatabase.COMPARE_TYPE_LIKE
	 * @return current database for where chain
	 */
	public abstract IDatabase where(String fieldName, Object value, OperatorEnum compareType);

	/**
	 * Add a where clause. This clause is an "or" clause.
	 * The compare type of this method is IDatabase.COMPARE_TYPE_EQUAL. <br>
	 * This medhod assumes that if the string of <i>value</i> is the same with any column name, <br> 
	 * the <i>value</i> denotes the column not the string. If you want to use the string of the <br> 
	 * <i>value</i> instead of the column, add single quotes around <i>value</i>.
	 * 
	 * @param fieldName the column name
	 * @param value value of field
	 * @return current database for where chain
	 */
	public abstract IDatabase orWhere(String fieldName, Object value);
	
	/**
	 * Add a where clause. This clause is an "or" clause. 
	 * This medhod assumes that if the string of <i>value</i> is the same with any column name, <br> 
	 * the <i>value</i> denotes the column not the string. If you want to use the string of the <br> 
	 * <i>value</i> instead of the column, add single quotes around <i>value</i>.
	 * 
	 * @param fieldName the column name
	 * @param value value of field
	 * @param compareType	IDatabase.COMPARE_TYPE_EQUAL, IDatabase.COMPARE_TYPE_UNEQUAL
	 * 						, IDatabase.COMPARE_TYPE_GREATER_THAN, IDatabase.COMPARE_TYPE_LESS_THAN,
	 * 						IDatabase.COMPARE_TYPE_LIKE
	 * @return current database for where chain
	 */
	public abstract IDatabase orWhere(String fieldName, Object value, OperatorEnum compareType);
	
	/**
	 * Clear all where clauses.
	 */
	public abstract void clear();

}
